package tools;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import net.sf.json.JSONObject;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

public class Lolita {
	
    public static Lolita lolita=new Lolita();
    
    public JSONArray GetLolita(String formname) throws SQLException {
        Connection conn = new DBUtil().getConnection();
        PreparedStatement ps = conn.prepareStatement("select * from " + formname);
        ResultSet rs = ps.executeQuery();
        JSONArray jsonlist = new JSONArray();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        if (rs != null) {
            while (rs.next()) {
                JSONObject jsonObj = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = rs.getString(columnName);
                    jsonObj.put(columnName, value);
                }
                jsonlist.add(jsonObj);
            }
        }
        System.out.println("洛丽塔:" + jsonlist);
        rs.close();
        ps.close();
        conn.close();
        return jsonlist;
    }

    public JSONArray GetLolita(String formname, String searchName) throws SQLException {
        System.out.println("洛丽塔:" + searchName);
        Connection conn = new DBUtil().getConnection();
        PreparedStatement ps = conn.prepareStatement("select * from " + formname);
        ResultSet rs = ps.executeQuery();
        JSONArray jsonlist = new JSONArray();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        String attr = "(";
        if (rs != null) {
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    attr = attr + columnName + ",";
                }
                if ((attr.charAt(attr.length() - 1)) == ',') {
                    attr = attr.substring(0, attr.length() - 1);
                }
                attr = attr + ")";
                break;
            }
        }
        System.out.println("洛丽塔:" + attr);
        ps = conn.prepareStatement("SELECT * FROM " + formname + " WHERE CONCAT" + attr + " LIKE '%" + searchName + "%' ");
        rs = ps.executeQuery();
        jsonlist = new JSONArray();
        metaData = rs.getMetaData();
        columnCount = metaData.getColumnCount();
        if (rs != null) {
            while (rs.next()) {
                JSONObject jsonObj = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = rs.getString(columnName);
                    jsonObj.put(columnName, value);
                }
                jsonlist.add(jsonObj);
            }
        }
        System.out.println("洛丽塔:" + jsonlist);
        rs.close();
        ps.close();
        conn.close();
        return jsonlist;
    }

    public JSONArray GetLolita(String formname, String searchName, String searchColumnName) throws SQLException {
        Connection conn = new DBUtil().getConnection();
        PreparedStatement ps = conn.prepareStatement("select * from " + formname + " where " + searchColumnName + " = \'" + searchName + "\'");
        ResultSet rs = ps.executeQuery();
        JSONArray jsonlist = new JSONArray();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        if (rs != null) {
            while (rs.next()) {
                JSONObject jsonObj = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = rs.getString(columnName);
                    jsonObj.put(columnName, value);
                }
                jsonlist.add(jsonObj);
            }
        }
        System.out.println("洛丽塔:" + jsonlist);
        rs.close();
        ps.close();
        conn.close();
        return jsonlist;
    }

    public JSONObject ObjToJson(Object object) throws SQLException {
        String jsonOutput = JSON.toJSONString(object);
        JSONObject jsonObj = JSONObject.fromObject(JSON.parseObject(jsonOutput));
        return jsonObj;
    }
    
/*
    public void AddLolita(JSONObject jsonObj) throws SQLException {
//        BigDecimal big = new BigDecimal("0");
//        ModelObj model = new ModelObj(0, 0, "", "", big, 0);
        KeyModel model=new KeyModel(0);
        String jsonOutput = JSON.toJSONString(model);
        JSONObject modeljsonObj = JSONObject.fromObject(JSON.parseObject(jsonOutput));
        Iterator it = modeljsonObj.keys();
        List<String> modelkeyList = new ArrayList<String>();
        while (it.hasNext()) {
            modelkeyList.add(it.next().toString());
        }
        it = jsonObj.keys();
        Connection conn = new DBUtil().getConnection();
        PreparedStatement ps = conn.prepareStatement("select number from objall where id = '1'");
        ResultSet rs = ps.executeQuery();
        int objnumber = 0;
        if (rs != null) {
            while (rs.next()) {
                objnumber = rs.getInt("number");
                break;
            }
        }
        ps = conn.prepareStatement("update objall set number=number+1 where id ='1'");
        ps.executeUpdate();
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("DROP TABLE obj" + objnumber);
        } catch (SQLException throwables) {
        }

        String sqlstr = "CREATE TABLE obj" + objnumber + " (\n" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
//                "  `adderid` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,\n" +
//                "  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,\n" +
//                "  `intro` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,\n" +
//                "  `price` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,\n" +
//                "  `number` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,\n" +
//                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;\n";

        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sqlstr);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        while (it.hasNext()) {
            String attr = it.next().toString();
            if (modelkeyList.contains(attr)) {
                System.out.println("洛丽塔:" + "attr " + attr + " has been exist");
            } else {
                System.out.println("洛丽塔:" + attr);
                String attrStr = "alter table obj" + objnumber + " add " + attr + " varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL";
                System.out.println("洛丽塔:" + attrStr);
                ps = conn.prepareStatement(attrStr);
                ps.executeUpdate();
            }
        }
        rs.close();
        ps.close();
        conn.close();
    }
*/
    
    public void AddLolita(String tablename, JSONObject insertJsonObj) throws SQLException {
        Connection conn = new DBUtil().getConnection();
        PreparedStatement ps = conn.prepareStatement("select * from " + tablename);
        ResultSet rs = ps.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        String[] strarr = new String[]{"id", "num", "number", "money", "adderid"};
        String attr = "(";
        String attrv = "(";
        if (rs != null) {
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i);
                if (!columnName.equals("id")) {
                    attr = attr + columnName + ",";
                    if (Arrays.asList(strarr).contains(insertJsonObj.get(columnName))) {
                        attrv = attrv + insertJsonObj.get(columnName) + ",";
                    } else {
                        attrv = attrv + "\'" + insertJsonObj.get(columnName) + "\',";
                    }
                }
            }
        }
        if ((attr.charAt(attr.length() - 1)) == ',') {
            attr = attr.substring(0, attr.length() - 1);
        }
        attr = attr + ")";
        if ((attrv.charAt(attrv.length() - 1)) == ',') {
            attrv = attrv.substring(0, attrv.length() - 1);
        }
        attrv = attrv + ")";
        System.out.println("洛丽塔:" + attr);
        System.out.println("洛丽塔:" + attrv);
        String sql = "INSERT INTO " + tablename + attr + "VALUES" + attrv + ";";
        System.out.println("洛丽塔:" + sql);
        ps = conn.prepareStatement(sql);
        ps.executeUpdate();
        rs.close();
        ps.close();
        conn.close();
    }

    public void AddLolita(String formname, String attr) throws SQLException {
        Connection conn = new DBUtil().getConnection();
        String sql = "ALTER TABLE "+formname+" ADD "+attr+" varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL";
        System.out.println("洛丽塔:" + sql);
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

    public void ChangeLolita(String formname,int id, JSONObject value) throws SQLException {
        Connection conn = new DBUtil().getConnection();
        PreparedStatement ps = conn.prepareStatement("select * from " + formname);
        ResultSet rs = ps.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        String[] strarr = new String[]{"id", "num", "number", "money", "adderid"};
        String sqlpart="";

        if (rs != null) {
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i);
                if (!columnName.equals("id")) {
                    if (Arrays.asList(strarr).contains(value.get(columnName))) {
                        sqlpart = sqlpart + columnName + "=";
                        sqlpart = sqlpart + value.get(columnName) + ",";
                    } else {
                        sqlpart = sqlpart + columnName + "=";
                        sqlpart = sqlpart + "\'" + value.get(columnName) + "\',";
                    }
                }

            }
        }
        if ((sqlpart.charAt(sqlpart.length() - 1)) == ',') {
            sqlpart = sqlpart.substring(0, sqlpart.length() - 1);
        }



        String sql = "UPDATE " + formname + " set "+sqlpart+ "where id ="+id+";";
        System.out.println("洛丽塔:" + sql);
        ps = conn.prepareStatement(sql);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

    public void ChangeLolita(String formname, String attrname, int id, String value, String type) throws SQLException {
        Connection conn = new DBUtil().getConnection();
        PreparedStatement ps = null;
        String sqlstr = "";
        if (type.equals("add") || type.equals("cut")) {
            boolean addModel = true;
            if (type.equals("cut")) {
                addModel = false;
            }
            if (addModel) {
                sqlstr = "update " + formname + " set " + attrname + "=" + attrname + "+" + value + " where id=" + id;
                ps = conn.prepareStatement(sqlstr);
            } else {
                sqlstr = "update " + formname + " set " + attrname + "=" + attrname + "-" + value + " where id=" + id;
                ps = conn.prepareStatement(sqlstr);
            }
        } else {
            sqlstr = "update " + formname + " set " + attrname + "=\'" + value + "\' where id=" + id;
            System.out.println("洛丽塔:" + sqlstr);
            ps = conn.prepareStatement(sqlstr);
        }
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

    public void DelLolita(String formname, String attrname, String value) throws SQLException {
        Connection conn = new DBUtil().getConnection();
        PreparedStatement ps = null;
        String sqlstr = "";
        sqlstr = "DELETE from " + formname + " where " + attrname + " = " + value;
        System.out.println("洛丽塔:" + sqlstr);
        ps = conn.prepareStatement(sqlstr);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
}
